Note: This page is only for my DATA 2401 project.
I.Introduction
“Immigration is not just a link to America’s past; it’s also a bridge to America’s future.” – George W. Bush
Note: The President spoke about the Immigration Act of 1990 in the Roosevelt Room at the White House. The Act was approved November 29, and was assigned Public Law No. 101 - 649. After that, it made big changes to U.S. immigration laws by allowing more people to immigrate and creating a better-organized system for work-related immigration.
1. About the H1B Program
The H1B program is a U.S. work visa designed for skilled foreign professionals. It allows companies to hire international workers in specialized fields like IT, engineering, medicine, and finance. Employers must prove that hiring these workers will not negatively impact the U.S. job market.
2. What is the LCA?
The Labor Condition Application (LCA) is a document that employers must file with the Department of Labor (DOL) as part of the H1B visa process (LCA is a requisite of H1B petition). The LCA confirms that the employer will pay the foreign worker fairly and that the hiring won’t harm U.S. workers’ wages or working conditions. Remember: When employee obtained LCA (certified status) from DOL, it does not mean that the employee has done the H1B visa’s process.
3. The Process of H1B Visa
The H1B process includes several steps:
The employer files an LCA with the DOL.
After the LCA is approved, the employer registers the worker in the H1B lottery system during the USCIS registration period.
USCIS randomly selects registrants for processing. Only those selected can proceed to the next step.
The employer submits the H1B petition to USCIS and the petition review by USCIS.
The worker applies for the H1B visa at a U.S. embassy or consulate in their home country. If granted, they can begin employment.
You can read more information about H1B process (USCIS).
4.Something is very interesting to you?
H1B Cap-Exempt vs. Regular Employer:
1.H1B Cap-Exempt Employer:
These employers are not subject to the annual H1B visa limit Cap Season
Typically includes universities, nonprofit research institutions, and government research organizations.
Applications can be filed any time of the year without going through the H1B lottery.
2.H1B Regular Employer:
These employers fall under the annual H1B visa cap.
Includes private companies and for-profit organizations.
Workers must be selected through the lottery process to proceed with their visa petition if applications exceed the cap.
For example:
H1B Regular Employer in Houston: Deloitte, Ernst & Young, PWC, KPMG, etc.
H1B Cap-Exempt Employer in Houston: MD Anderson, University of Houston System, Houston Independent School District, etc.
II. About the LCA Dataset
1. Where is the original data?
The original LCA dataset is publicly available from the U.S. Department of Labor.It is prepared and managed by the Office of Foreign Labor Certification (OFLC) under the Department’s Employment and Training Administration. This dataset provides detailed information about H1B applications, including job titles, wages, worksite locations, etc.
Link to view/download the dataset: LCA Dataset
2. Why my team chose this dataset?
The dataset we selected focuses on data from 2024 to Q1 of 2025, making it ideal for uncovering key trends in H1B applications during this time period. Our aim is to conduct a detailed analysis to understand:
1.Application Outcomes: Examining the trends in certified, denied, and withdrawn applications, and identifying any patterns.
2. State-Level Insights: Analyzing how H1B applications are distributed across all U.S. states to determine geographic hotspots for employment-based immigration.
3.Top Sponsoring Companies: Highlighting the companies that filed the most applications, providing insights into industries or organizations most reliant on H1B workers.
4.Quarterly Trends: Breaking down the data by quarter to observe how application numbers and outcomes fluctuate over time. This approach allows us to spot seasonal trends or shifts in demand.
5.Wage and Occupation Analysis: Investigating the offered prevailing wages and job categories (e.g., technology, healthcare) to understand how they align with industry standards and labor market demands.
3. What are the variables in the filtered data and its meaning?
- Case Information:
CASE_NUMBER: A unique identifier for each Labor Condition Application (LCA).
CASE_STATUS: The application status (e.g., Certified, Denied, Withdrawn).
- Date Information:
RECEIVED_DATE: The date the LCA application was submitted.
DECISION_DATE: The date a decision was made on the application.
- Job and Visa Details:
VISA_CLASS: The type of visa, such as H1B.
JOB_TITLE: The official job title for the position being filled.
SOC_CODE: The Standard Occupational Classification (SOC) code for the job.
SOC_TITLE: The title corresponding to the SOC code, describing the job category.
FULL_TIME_POSITION: Indicates if the position is full-time (Yes/No).
BEGIN_DATE: The date the employment starts.
END_DATE: The date the employment ends.
TOTAL_WORKER_POSITIONS: The total number of workers requested for the role.
- Employer Information:
EMPLOYER_NAME: Name of the company or organization submitting the LCA.
EMPLOYER_ADDRESS1: Address of the employer.
EMPLOYER_CITY: City where the employer is located.
EMPLOYER_STATE: State where the employer is located.
EMPLOYER_POSTAL_CODE: Postal code of the employer’s location.
NAICS_CODE: North American Industry Classification System (NAICS) code representing the industry the employer operates in.
- Legal Representation:
AGENT_REPRESENTING_EMPLOYER: Indicates if an agent represents the employer (Yes/No).
LAWFIRM_NAME_BUSINESS_NAME: Name of the law firm or representative assisting with the application.
- Worksite Information:
WORKSITE_WORKERS: Number of workers at the job location.
WORKSITE_CITY: City where the job is located.
WORKSITE_STATE: State where the job is located.
WORKSITE_POSTAL_CODE: Postal code for the job location.
- Wage Details:
WAGE_RATE_OF_PAY_FROM: The starting wage offered for the position.
WAGE_RATE_OF_PAY_TO: The maximum wage offered for the position (if applicable).
WAGE_UNIT_OF_PAY: The frequency of pay (e.g., hourly, yearly).
PREVAILING_WAGE: The standard wage for similar roles in the area.
PW_UNIT_OF_PAY: The unit of pay for the prevailing wage (e.g., hourly, yearly).
PW_WAGE_LEVEL: The skill level associated with the prevailing wage from DOL (e.g., Level 1–4).
PW_OES_YEAR: The year the prevailing wage data was sourced from the Occupational Employment Statistics (OES).
III. GET READY TO LAUNCH THE DATA!
file_path <- "C:\\Users\\lequa\\Downloads\\LCA H1B FY2024-2025\\"
csv_files <- list.files(path = file_path, pattern = "\\.csv$", full.names = TRUE)
LCA_2024_2025_data <- csv_files %>%
lapply(read.csv) %>%
bind_rows %>%
select(CASE_NUMBER, CASE_STATUS, RECEIVED_DATE, DECISION_DATE, VISA_CLASS,
JOB_TITLE, SOC_CODE, SOC_TITLE, FULL_TIME_POSITION, BEGIN_DATE, END_DATE,
TOTAL_WORKER_POSITIONS, EMPLOYER_NAME, EMPLOYER_CITY,EMPLOYER_STATE, EMPLOYER_POSTAL_CODE, EMPLOYER_COUNTRY,
NAICS_CODE, AGENT_REPRESENTING_EMPLOYER, LAWFIRM_NAME_BUSINESS_NAME,
WORKSITE_WORKERS, WORKSITE_CITY, WORKSITE_STATE,
WORKSITE_POSTAL_CODE, WAGE_RATE_OF_PAY_FROM, WAGE_RATE_OF_PAY_TO,
WAGE_UNIT_OF_PAY, PREVAILING_WAGE, PW_UNIT_OF_PAY, PW_WAGE_LEVEL,
PW_OES_YEAR) %>%
filter(!if_all(everything(), ~is.na(.) | . == ""))
glimpse(LCA_2024_2025_data)## Rows: 668,451
## Columns: 31
## $ CASE_NUMBER <chr> "I-200-23080-866996", "I-200-23364-599927"…
## $ CASE_STATUS <chr> "Certified - Withdrawn", "Withdrawn", "Cer…
## $ RECEIVED_DATE <chr> "3/21/2023", "12/30/2023", "12/21/2023", "…
## $ DECISION_DATE <chr> "12/31/2023", "12/30/2023", "12/29/2023", …
## $ VISA_CLASS <chr> "H-1B", "H-1B", "E-3 Australian", "H-1B", …
## $ JOB_TITLE <chr> "Biochemical Research Associate", "Sr. Mec…
## $ SOC_CODE <chr> "19-1021.00", "17-2141.00", "15-1244.00", …
## $ SOC_TITLE <chr> "Biochemists and Biophysicists", "Mechanic…
## $ FULL_TIME_POSITION <chr> "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y…
## $ BEGIN_DATE <chr> "4/15/2023", "1/10/2024", "12/21/2023", "6…
## $ END_DATE <chr> "4/14/2026", "1/9/2027", "12/20/2025", "6/…
## $ TOTAL_WORKER_POSITIONS <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
## $ EMPLOYER_NAME <chr> "Meharry Medical College", "Tesla, Inc.", …
## $ EMPLOYER_CITY <chr> "Nashville", "Austin", "NORMAL", "Secaucus…
## $ EMPLOYER_STATE <chr> "TN", "TX", "IL", "NJ", "CA", "WA", "VA", …
## $ EMPLOYER_POSTAL_CODE <chr> "37208", "78725", "61761", "07094", "95014…
## $ EMPLOYER_COUNTRY <chr> "UNITED STATES OF AMERICA", "UNITED STATES…
## $ NAICS_CODE <int> 611310, 336111, 541511, 541211, 334111, 51…
## $ AGENT_REPRESENTING_EMPLOYER <chr> "Yes", "Yes", "No", "Yes", "Yes", "No", "Y…
## $ LAWFIRM_NAME_BUSINESS_NAME <chr> "Olsen law firm", "Seyfarth Shaw LLP", "",…
## $ WORKSITE_WORKERS <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
## $ WORKSITE_CITY <chr> "Nashville", "Fremont", "BLOOMINGTON", "Je…
## $ WORKSITE_STATE <chr> "TN", "CA", "MN", "NJ", "CA", "CA", "CA", …
## $ WORKSITE_POSTAL_CODE <chr> "37208", "94538", "55437", "07311", "90016…
## $ WAGE_RATE_OF_PAY_FROM <chr> "$62,480.00 ", "$141,080.00 ", "$88,712.00…
## $ WAGE_RATE_OF_PAY_TO <chr> "", "", "", "", "$198,100.00 ", "", "", "$…
## $ WAGE_UNIT_OF_PAY <chr> "Year", "Year", "Year", "Year", "Year", "Y…
## $ PREVAILING_WAGE <chr> "$60,424.00 ", "$141,080.00 ", "$81,931.00…
## $ PW_UNIT_OF_PAY <chr> "Year", "Year", "Year", "Year", "Year", "Y…
## $ PW_WAGE_LEVEL <chr> "I", "", "II", "II", "III", "III", "II", "…
## $ PW_OES_YEAR <chr> "7/1/2022 - 6/30/2023", "", "7/1/2023 - 6/…IV. Questions for data analysis
1.1. How many case status were certified, Certified Withdrawn, Denied, and Withdrawn (by State)?
count_cases <- LCA_2024_2025_data %>% group_by(EMPLOYER_STATE) %>%
select(CASE_NUMBER, CASE_STATUS, EMPLOYER_STATE) %>%
summarize(
Certified_Cases = sum(CASE_STATUS=="Certified"),
Certified_Withdrawn_Cases = sum(CASE_STATUS=="Certified - Withdrawn"),
Withdraw_Cases= sum(CASE_STATUS=="Withdrawn"),
Denied_Cases = sum(CASE_STATUS=="Denied"),
Total_Cases = n()
) %>% rename(
"Employer States" = EMPLOYER_STATE,
"Certified Cases" = Certified_Cases,
"Certified Withdrawn Cases" = Certified_Withdrawn_Cases,
"Withdraw Cases" = Withdraw_Cases,
"Denied Cases" = Denied_Cases,
"Total Case" = Total_Cases )
datatable(count_cases,
options = list(pageLength = 10,
scrollX = TRUE),
rownames = FALSE)1.2. How many case status were certified, Certified Withdrawn, Denied, and Withdrawn with Emmpty States (by Country and Visa class)?
missing_state_cases <- LCA_2024_2025_data %>%
filter(is.na(EMPLOYER_STATE) | EMPLOYER_STATE == "") %>% select(CASE_NUMBER,CASE_STATUS,VISA_CLASS,EMPLOYER_CITY,EMPLOYER_COUNTRY) %>%
rename(
"Case Number"=CASE_NUMBER,
"Case Status"=CASE_STATUS,
"Visa Class"=VISA_CLASS,
"Country Name"= EMPLOYER_COUNTRY,
"City Name" = EMPLOYER_CITY )
datatable(missing_state_cases,
options = list(pageLength = 10,
scrollX = TRUE),
rownames = FALSE)1.3. Demonstrate the trend of case status through quarter by quarter.
case_trends <- LCA_2024_2025_data %>% mutate(DECISION_DATE = as.Date(DECISION_DATE, format = "%m/%d/%Y")) %>%
mutate(Quarter = case_when(
DECISION_DATE >= as.Date("2024-10-01") & DECISION_DATE <= as.Date("2024-12-31") ~ "Q1-2025",
DECISION_DATE >= as.Date("2024-07-01") & DECISION_DATE <= as.Date("2024-09-30") ~ "Q4-2024",
DECISION_DATE >= as.Date("2024-04-01") & DECISION_DATE <= as.Date("2024-06-30") ~ "Q3-2024",
DECISION_DATE >= as.Date("2024-01-01") & DECISION_DATE <= as.Date("2024-03-31") ~ "Q2-2024",
DECISION_DATE >= as.Date("2023-10-02") & DECISION_DATE <= as.Date("2023-12-31") ~ "Q1-2024",
TRUE ~ NA_character_
)) %>%
group_by(Quarter, CASE_STATUS) %>%
summarize(Total_Cases = n(), .groups = "drop")
datatable(case_trends,
options = list(pageLength = 10,
scrollX = TRUE),
rownames = FALSE)p <- ggplot(case_trends, aes(x = Quarter, y = Total_Cases, fill = CASE_STATUS,
text = paste("Cases:", Total_Cases, "<br>Status:", CASE_STATUS))) +
geom_bar(stat = "identity") +
labs(title = "Case Status Trend by Quarter",
x = "Quarter",
y = "Total Cases") +
theme_minimal() +
scale_fill_manual(values = c("Certified" = "green", "Certified - Withdrawn" = "pink", "Withdrawn" = "orange", "Denied" = "red"))
interactive_plot_1 <- ggplotly(p, tooltip = "text")
interactive_plot_1